# coding=utf-8

import sys
import MySQLdb as db
import csv


def parse_csv(csvfile):
    with open(csvfile, 'r') as pf:
        reader = csv.reader(pf, delimiter=',')
        header = next(reader)
        csvdata = []
        for row in reader:
            csvdata.append(row)
    return header, csvdata


# 链接数据库
def mysqldb_operator(data):
    conn = db.connect(host="localhost", user="root", passwd="", db="xbqr", charset="utf8")
    print(conn)
    print("datebase connect success!")
    curs = conn.cursor()
    # 创建表之前先删除表
    curs.execute("drop table IF EXISTS table_xbqr")
    conn.commit()
    # 创建表
    query = "create table table_xbqr(\
        SupplierName VARCHAR(32),\
        InvoiceNumber VARCHAR(32),\
        PartNumber VARCHAR(32),\
        Cost VARCHAR(32),\
        PurchaseDate DATE)"
    curs.execute(query)
    conn.commit()
    for row in data:
        curs.execute("INSERT INTO table_xbqr VALUES(%s,%s,%s,%s,%s);", row)
        conn.commit()
    curs.execute("select * from table_xbqr")
    conn.commit()
    selectdata = curs.fetchall()
    print(selectdata)


if __name__ == "__main__":
    csvfile = sys.argv[1]
    print("csv file name:", csvfile)
    header, data = parse_csv(csvfile)
    print("header:")
    print(header)
    print("csvdata:")
    print(data)
    mysqldb_operator(data)